﻿using System;
using System.Collections.Generic;
using System.Linq;
using Model;
using DBUtility;
using MySql.Data.MySqlClient;

namespace DAL
{
    public class ProductRepository
    {
        // 添加产品
        public int AddProduct(Product product)
        {
            string sql = @"INSERT INTO product 
                            (FlowId, StationId, TotalProcessId, MaterialId, EntryAndExitStatus, ProductDescription, ProductType, Unit, PackagingForm) 
                           VALUES 
                            (@FlowId, @StationId, @TotalProcessId, @MaterialId, @EntryAndExitStatus, @ProductDescription, @ProductType, @Unit, @PackagingForm)";

            MySqlParameter[] parameters = {
                new MySqlParameter("@FlowId", product.FlowId),
                new MySqlParameter("@StationId", product.StationId),
                new MySqlParameter("@TotalProcessId", product.TotalProcessId),
                new MySqlParameter("@MaterialId", product.MaterialId),
                new MySqlParameter("@EntryAndExitStatus", product.EntryAndExitStatus),
                new MySqlParameter("@ProductDescription", product.ProductDescription ?? (object)DBNull.Value),
                new MySqlParameter("@ProductType", product.ProductType),
                new MySqlParameter("@Unit", product.Unit),
                new MySqlParameter("@PackagingForm", product.PackagingForm)
            };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        // 删除产品
        public int DeleteProduct(int productId)
        {
            string sql = "DELETE FROM product WHERE ProductId = @ProductId";
            MySqlParameter[] parameters = { new MySqlParameter("@ProductId", productId) };
            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        // 更新产品信息
        public int UpdateProduct(Product product)
        {
            string sql = @"UPDATE product 
                            SET FlowId = @FlowId, 
                                StationId = @StationId, 
                                TotalProcessId = @TotalProcessId, 
                                MaterialId = @MaterialId, 
                                EntryAndExitStatus = @EntryAndExitStatus, 
                                ProductDescription = @ProductDescription, 
                                ProductType = @ProductType, 
                                Unit = @Unit, 
                                PackagingForm = @PackagingForm 
                          WHERE ProductId = @ProductId";

            MySqlParameter[] parameters = {
                new MySqlParameter("@FlowId", product.FlowId),
                new MySqlParameter("@StationId", product.StationId),
                new MySqlParameter("@TotalProcessId", product.TotalProcessId),
                new MySqlParameter("@MaterialId", product.MaterialId),
                new MySqlParameter("@EntryAndExitStatus", product.EntryAndExitStatus),
                new MySqlParameter("@ProductDescription", product.ProductDescription ?? (object)DBNull.Value),
                new MySqlParameter("@ProductType", product.ProductType),
                new MySqlParameter("@Unit", product.Unit),
                new MySqlParameter("@PackagingForm", product.PackagingForm),
                new MySqlParameter("@ProductId", product.ProductId)
            };

            return DBHelper.ExecuteNonQuery(sql, parameters);
        }

        // 查询所有产品
        public List<Product> GetAllProducts()
        {
            string sql = "SELECT * FROM product";
            return DBHelper.ExecuteQuery(sql, null, MapProduct);
        }

        // 查询单个产品
        public Product GetProductById(int productId)
        {
            string sql = "SELECT * FROM product WHERE ProductId = @ProductId";
            MySqlParameter[] parameters = { new MySqlParameter("@ProductId", productId) };
            var products = DBHelper.ExecuteQuery(sql, parameters, MapProduct);
            return products.FirstOrDefault();
        }

        // 映射数据库查询结果到 Product 对象
        private Product MapProduct(MySqlDataReader reader)
        {
            return new Product
            {
                ProductId = (int)reader["ProductId"],
                FlowId = (int)reader["FlowId"],
                StationId = (int)reader["StationId"],
                TotalProcessId = (int)reader["TotalProcessId"],
                MaterialId = (int)reader["MaterialId"],
                EntryAndExitStatus = reader["EntryAndExitStatus"].ToString(),
                ProductDescription = reader["ProductDescription"] is DBNull ? null : reader["ProductDescription"].ToString(),
                ProductType = reader["ProductType"].ToString(),
                Unit = reader["Unit"].ToString(),
                PackagingForm = reader["PackagingForm"].ToString()
            };
        }
    }
}